{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3f8580a6",
   "metadata": {},
   "source": [
    "```\n",
    "Licensed to the Apache Software Foundation (ASF) under one\n",
    "or more contributor license agreements.  See the NOTICE file\n",
    "distributed with this work for additional information\n",
    "regarding copyright ownership.  The ASF licenses this file\n",
    "to you under the Apache License, Version 2.0 (the\n",
    "\"License\"); you may not use this file except in compliance\n",
    "with the License.  You may obtain a copy of the License at\n",
    "  http://www.apache.org/licenses/LICENSE-2.0\n",
    "Unless required by applicable law or agreed to in writing,\n",
    "software distributed under the License is distributed on an\n",
    "\"AS IS\" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY\n",
    "KIND, either express or implied.  See the License for the\n",
    "specific language governing permissions and limitations\n",
    "under the License.\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "216f0262",
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.display import display, HTML\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark import StorageLevel\n",
    "import pandas as pd\n",
    "from pyspark.sql.types import (\n",
    "    StructType,\n",
    "    StructField,\n",
    "    StringType,\n",
    "    LongType,\n",
    "    IntegerType,\n",
    "    DoubleType,\n",
    "    ArrayType,\n",
    ")\n",
    "from pyspark.sql.functions import regexp_replace\n",
    "from sedona.spark import SedonaRegistrator\n",
    "from sedona.spark import SedonaKryoRegistrator, KryoSerializer\n",
    "from pyspark.sql.functions import col, split, expr\n",
    "from pyspark.sql.functions import udf, lit\n",
    "from sedona.spark import SedonaKryoRegistrator, KryoSerializer\n",
    "from pyspark.sql.functions import col, split, expr\n",
    "from pyspark.sql.functions import udf, lit, flatten\n",
    "from pywebhdfs.webhdfs import PyWebHdfsClient\n",
    "from datetime import date\n",
    "from pyspark.sql.functions import monotonically_increasing_id\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4d87582c",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Warning: Ignoring non-Spark config property:  spark.sql.adaptive.coalescePartitions.enabled\n",
      "Ivy Default Cache set to: /root/.ivy2/cache\n",
      "The jars for the packages stored in: /root/.ivy2/jars\n",
      ":: loading settings :: url = jar:file:/usr/local/lib/python3.9/dist-packages/pyspark/jars/ivy-2.4.0.jar!/org/apache/ivy/core/settings/ivysettings.xml\n",
      "org.apache.sedona#sedona-python-adapter-3.0_2.12 added as a dependency\n",
      "org.datasyslab#geotools-wrapper added as a dependency\n",
      ":: resolving dependencies :: org.apache.spark#spark-submit-parent-c20579c1-d499-4129-ae06-0876b2e0525a;1.0\n",
      "\tconfs: [default]\n",
      "\tfound org.apache.sedona#sedona-python-adapter-3.0_2.12;1.1.0-incubating in central\n",
      "\tfound org.locationtech.jts#jts-core;1.18.0 in central\n",
      "\tfound org.wololo#jts2geojson;0.16.1 in central\n",
      "\tfound com.fasterxml.jackson.core#jackson-databind;2.12.2 in central\n",
      "\tfound com.fasterxml.jackson.core#jackson-annotations;2.12.2 in central\n",
      "\tfound com.fasterxml.jackson.core#jackson-core;2.12.2 in central\n",
      "\tfound org.apache.sedona#sedona-core-3.0_2.12;1.1.0-incubating in central\n",
      "\tfound org.apache.sedona#sedona-sql-3.0_2.12;1.1.0-incubating in central\n",
      "\tfound org.datasyslab#geotools-wrapper;1.1.0-25.2 in central\n",
      ":: resolution report :: resolve 227ms :: artifacts dl 4ms\n",
      "\t:: modules in use:\n",
      "\tcom.fasterxml.jackson.core#jackson-annotations;2.12.2 from central in [default]\n",
      "\tcom.fasterxml.jackson.core#jackson-core;2.12.2 from central in [default]\n",
      "\tcom.fasterxml.jackson.core#jackson-databind;2.12.2 from central in [default]\n",
      "\torg.apache.sedona#sedona-core-3.0_2.12;1.1.0-incubating from central in [default]\n",
      "\torg.apache.sedona#sedona-python-adapter-3.0_2.12;1.1.0-incubating from central in [default]\n",
      "\torg.apache.sedona#sedona-sql-3.0_2.12;1.1.0-incubating from central in [default]\n",
      "\torg.datasyslab#geotools-wrapper;1.1.0-25.2 from central in [default]\n",
      "\torg.locationtech.jts#jts-core;1.18.0 from central in [default]\n",
      "\torg.wololo#jts2geojson;0.16.1 from central in [default]\n",
      "\t:: evicted modules:\n",
      "\torg.locationtech.jts#jts-core;1.18.1 by [org.locationtech.jts#jts-core;1.18.0] in [default]\n",
      "\t---------------------------------------------------------------------\n",
      "\t|                  |            modules            ||   artifacts   |\n",
      "\t|       conf       | number| search|dwnlded|evicted|| number|dwnlded|\n",
      "\t---------------------------------------------------------------------\n",
      "\t|      default     |   10  |   0   |   0   |   1   ||   9   |   0   |\n",
      "\t---------------------------------------------------------------------\n",
      ":: retrieving :: org.apache.spark#spark-submit-parent-c20579c1-d499-4129-ae06-0876b2e0525a\n",
      "\tconfs: [default]\n",
      "\t0 artifacts copied, 9 already retrieved (0kB/5ms)\n",
      "22/02/01 12:43:55 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable\n",
      "Using Spark's default log4j profile: org/apache/spark/log4j-defaults.properties\n",
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n",
      "22/02/01 12:43:56 WARN SQLConf: The SQL config 'spark.sql.execution.arrow.fallback.enabled' has been deprecated in Spark v3.0 and may be removed in the future. Use 'spark.sql.execution.arrow.pyspark.fallback.enabled' instead of it.\n",
      "22/02/01 12:43:57 WARN SQLConf: The SQL config 'spark.sql.execution.arrow.fallback.enabled' has been deprecated in Spark v3.0 and may be removed in the future. Use 'spark.sql.execution.arrow.pyspark.fallback.enabled' instead of it.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "# spark.scheduler.mode', 'FAIR'\n",
    "spark = (\n",
    "    SparkSession.builder.appName(\"Overpass-API\")\n",
    "    .enableHiveSupport()\n",
    "    .master(\"local[*]\")\n",
    "    .master(\"spark://spark-master:7077\")\n",
    "    .config(\"spark.executor.memory\", \"15G\")\n",
    "    .config(\"spark.driver.maxResultSize\", \"135G\")\n",
    "    .config(\"spark.sql.shuffle.partitions\", \"500\")\n",
    "    .config(\" spark.sql.adaptive.coalescePartitions.enabled\", True)\n",
    "    .config(\"spark.sql.adaptive.enabled\", True)\n",
    "    .config(\"spark.sql.adaptive.coalescePartitions.initialPartitionNum\", 125)\n",
    "    .config(\"spark.sql.execution.arrow.pyspark.enabled\", True)\n",
    "    .config(\"spark.sql.execution.arrow.fallback.enabled\", True)\n",
    "    .config(\"spark.kryoserializer.buffer.max\", 2047)\n",
    "    .config(\"spark.serializer\", KryoSerializer.getName)\n",
    "    .config(\"spark.kryo.registrator\", SedonaKryoRegistrator.getName)\n",
    "    .config(\n",
    "        \"spark.jars.packages\",\n",
    "        \"org.apache.sedona:sedona-python-adapter-3.0_2.12:1.1.0-incubating,org.datasyslab:geotools-wrapper:1.1.0-25.2\",\n",
    "    )\n",
    "    .enableHiveSupport()\n",
    "    .getOrCreate()\n",
    ")\n",
    "\n",
    "SedonaRegistrator.registerAll(spark)\n",
    "sc = spark.sparkContext"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "bb49563e",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "import json\n",
    "\n",
    "overpass_url = \"http://overpass-api.de/api/interpreter\"\n",
    "# overpass_query = \"\"\"\n",
    "# [out:json];\n",
    "# area[\"ISO3166-1\"=\"DE\"][admin_level=2];\n",
    "# (node[\"amenity\"=\"biergarten\"](area);\n",
    "#  way[\"amenity\"=\"biergarten\"](area);\n",
    "#  rel[\"amenity\"=\"biergarten\"](area);\n",
    "# );\n",
    "# out center;\n",
    "# \"\"\"\n",
    "\n",
    "# overpass_query = \"\"\"\n",
    "# [out:json];\n",
    "# area[name = \"Foz do Iguaçu\"];\n",
    "# (way(area)[\"highway\"~\"^(private|cycleway|footway|bus_guideway|elevator|construction|proposed|bridleway|steps|raceway|motorway_link|path|secondary|motorway|trunk|primary)$\"];>;);\n",
    "# out center;\n",
    "# \"\"\"\n",
    "\n",
    "overpass_query = \"\"\"\n",
    "[out:json];\n",
    "area[name = \"Foz do Iguaçu\"];\n",
    "way(area)[\"highway\"~\"\"];\n",
    "out geom;\n",
    ">;\n",
    "out skel qt;\n",
    "\"\"\"\n",
    "\n",
    "# response = requests.get(overpass_url,\n",
    "#                         params={'data': overpass_query})\n",
    "# data = response.json()\n",
    "# hdfs = PyWebHdfsClient(host='179.106.229.159',port='50070', user_name='root')\n",
    "file_name = \"foz_roads_osm.json\"\n",
    "# hdfs.delete_file_dir(file_name)\n",
    "# hdfs.create_file(file_name, json.dumps(data))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "15f254fb",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "22/02/01 12:44:03 WARN package: Truncated the string representation of a plan since it was too large. This behavior can be adjusted by setting 'spark.sql.debug.maxToStringFields'.\n"
     ]
    }
   ],
   "source": [
    "path = \"hdfs://776faf4d6a1e:8020/\" + file_name\n",
    "df = spark.read.json(path, multiLine=\"true\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e9d3a649",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- elements: array (nullable = true)\n",
      " |    |-- element: struct (containsNull = true)\n",
      " |    |    |-- bounds: struct (nullable = true)\n",
      " |    |    |    |-- maxlat: double (nullable = true)\n",
      " |    |    |    |-- maxlon: double (nullable = true)\n",
      " |    |    |    |-- minlat: double (nullable = true)\n",
      " |    |    |    |-- minlon: double (nullable = true)\n",
      " |    |    |-- geometry: array (nullable = true)\n",
      " |    |    |    |-- element: struct (containsNull = true)\n",
      " |    |    |    |    |-- lat: double (nullable = true)\n",
      " |    |    |    |    |-- lon: double (nullable = true)\n",
      " |    |    |-- id: long (nullable = true)\n",
      " |    |    |-- lat: double (nullable = true)\n",
      " |    |    |-- lon: double (nullable = true)\n",
      " |    |    |-- nodes: array (nullable = true)\n",
      " |    |    |    |-- element: long (containsNull = true)\n",
      " |    |    |-- tags: struct (nullable = true)\n",
      " |    |    |    |-- FIXME: string (nullable = true)\n",
      " |    |    |    |-- access: string (nullable = true)\n",
      " |    |    |    |-- addr:city: string (nullable = true)\n",
      " |    |    |    |-- addr:postcode: string (nullable = true)\n",
      " |    |    |    |-- alt_name: string (nullable = true)\n",
      " |    |    |    |-- area: string (nullable = true)\n",
      " |    |    |    |-- barrier: string (nullable = true)\n",
      " |    |    |    |-- bicycle: string (nullable = true)\n",
      " |    |    |    |-- bridge: string (nullable = true)\n",
      " |    |    |    |-- bridge:structure: string (nullable = true)\n",
      " |    |    |    |-- bus: string (nullable = true)\n",
      " |    |    |    |-- covered: string (nullable = true)\n",
      " |    |    |    |-- crossing: string (nullable = true)\n",
      " |    |    |    |-- description: string (nullable = true)\n",
      " |    |    |    |-- destination: string (nullable = true)\n",
      " |    |    |    |-- destination:ref: string (nullable = true)\n",
      " |    |    |    |-- embankment: string (nullable = true)\n",
      " |    |    |    |-- fixme: string (nullable = true)\n",
      " |    |    |    |-- foot: string (nullable = true)\n",
      " |    |    |    |-- footway: string (nullable = true)\n",
      " |    |    |    |-- height: string (nullable = true)\n",
      " |    |    |    |-- hgv: string (nullable = true)\n",
      " |    |    |    |-- highway: string (nullable = true)\n",
      " |    |    |    |-- horse: string (nullable = true)\n",
      " |    |    |    |-- incline: string (nullable = true)\n",
      " |    |    |    |-- junction: string (nullable = true)\n",
      " |    |    |    |-- kerb: string (nullable = true)\n",
      " |    |    |    |-- lanes: string (nullable = true)\n",
      " |    |    |    |-- lanes:backward: string (nullable = true)\n",
      " |    |    |    |-- lanes:forward: string (nullable = true)\n",
      " |    |    |    |-- layer: string (nullable = true)\n",
      " |    |    |    |-- lit: string (nullable = true)\n",
      " |    |    |    |-- maxspeed: string (nullable = true)\n",
      " |    |    |    |-- motor_vehicle: string (nullable = true)\n",
      " |    |    |    |-- motorcar: string (nullable = true)\n",
      " |    |    |    |-- motorroad: string (nullable = true)\n",
      " |    |    |    |-- mtb:scale: string (nullable = true)\n",
      " |    |    |    |-- name: string (nullable = true)\n",
      " |    |    |    |-- name:en: string (nullable = true)\n",
      " |    |    |    |-- name:es: string (nullable = true)\n",
      " |    |    |    |-- name:etymology:wikidata: string (nullable = true)\n",
      " |    |    |    |-- name:pt: string (nullable = true)\n",
      " |    |    |    |-- noname: string (nullable = true)\n",
      " |    |    |    |-- note: string (nullable = true)\n",
      " |    |    |    |-- old_name: string (nullable = true)\n",
      " |    |    |    |-- oneway: string (nullable = true)\n",
      " |    |    |    |-- operator: string (nullable = true)\n",
      " |    |    |    |-- owner: string (nullable = true)\n",
      " |    |    |    |-- postal_code: string (nullable = true)\n",
      " |    |    |    |-- ref: string (nullable = true)\n",
      " |    |    |    |-- sac_scale: string (nullable = true)\n",
      " |    |    |    |-- segregated: string (nullable = true)\n",
      " |    |    |    |-- service: string (nullable = true)\n",
      " |    |    |    |-- sidewalk: string (nullable = true)\n",
      " |    |    |    |-- source: string (nullable = true)\n",
      " |    |    |    |-- source:name: string (nullable = true)\n",
      " |    |    |    |-- sport: string (nullable = true)\n",
      " |    |    |    |-- surface: string (nullable = true)\n",
      " |    |    |    |-- trail_visibility: string (nullable = true)\n",
      " |    |    |    |-- tunnel: string (nullable = true)\n",
      " |    |    |    |-- turn:lanes: string (nullable = true)\n",
      " |    |    |    |-- turn:lanes:forward: string (nullable = true)\n",
      " |    |    |    |-- vehicle: string (nullable = true)\n",
      " |    |    |    |-- wheelchair: string (nullable = true)\n",
      " |    |    |    |-- width: string (nullable = true)\n",
      " |    |    |    |-- wikidata: string (nullable = true)\n",
      " |    |    |    |-- wikipedia: string (nullable = true)\n",
      " |    |    |-- type: string (nullable = true)\n",
      " |-- generator: string (nullable = true)\n",
      " |-- osm3s: struct (nullable = true)\n",
      " |    |-- copyright: string (nullable = true)\n",
      " |    |-- timestamp_areas_base: timestamp (nullable = true)\n",
      " |    |-- timestamp_osm_base: timestamp (nullable = true)\n",
      " |-- version: double (nullable = true)\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4c886014",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+--------------------+-------+-----------+\n",
      "|            elements|           generator|               osm3s|version|total_nodes|\n",
      "+--------------------+--------------------+--------------------+-------+-----------+\n",
      "|[[[-25.5267745, -...|Overpass API 0.7....|[The data include...|    0.6|      36560|\n",
      "+--------------------+--------------------+--------------------+-------+-----------+\n",
      "\n",
      "36560\n",
      "26122619\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+--------------------+-------+-----------+--------+\n",
      "|            elements|           generator|               osm3s|version|total_nodes|      id|\n",
      "+--------------------+--------------------+--------------------+-------+-----------+--------+\n",
      "|[[[-25.5267745, -...|Overpass API 0.7....|[The data include...|    0.6|      36560|25835738|\n",
      "|[[[-25.5267745, -...|Overpass API 0.7....|[The data include...|    0.6|      36560|26122619|\n",
      "|[[[-25.5267745, -...|Overpass API 0.7....|[The data include...|    0.6|      36560|26122631|\n",
      "|[[[-25.5267745, -...|Overpass API 0.7....|[The data include...|    0.6|      36560|26122645|\n",
      "|[[[-25.5267745, -...|Overpass API 0.7....|[The data include...|    0.6|      36560|26122801|\n",
      "+--------------------+--------------------+--------------------+-------+-----------+--------+\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+--------------------+-------+-----------+--------------------+\n",
      "|            elements|           generator|               osm3s|version|total_nodes|                 new|\n",
      "+--------------------+--------------------+--------------------+-------+-----------+--------------------+\n",
      "|[[[-25.5267745, -...|Overpass API 0.7....|[The data include...|    0.6|      36560|[25835738, [[-25....|\n",
      "|[[[-25.5267745, -...|Overpass API 0.7....|[The data include...|    0.6|      36560|[26122619, [[-25....|\n",
      "|[[[-25.5267745, -...|Overpass API 0.7....|[The data include...|    0.6|      36560|[26122631, [[-25....|\n",
      "|[[[-25.5267745, -...|Overpass API 0.7....|[The data include...|    0.6|      36560|[26122645, [[-25....|\n",
      "|[[[-25.5267745, -...|Overpass API 0.7....|[The data include...|    0.6|      36560|[26122801, [[-25....|\n",
      "+--------------------+--------------------+--------------------+-------+-----------+--------------------+\n",
      "only showing top 5 rows\n",
      "\n",
      "+--------+--------------------+--------------------+--------+-------+--------+--------------------+-----------+\n",
      "|      id|                geom|               nodes|maxspeed|incline| surface|                name|total_nodes|\n",
      "+--------+--------------------+--------------------+--------+-------+--------+--------------------+-----------+\n",
      "|25835738|[[-25.5343718, -5...|[362528326, 28597...|    null|   null|    null|Avenida Costa e S...|      36560|\n",
      "|26122619|[[-25.5868602, -5...|[285975617, 59202...|      60|   null|   paved|    Avenida Mercosul|      36560|\n",
      "|26122631|[[-25.5868602, -5...|[285975617, 69310...|      60|   null|concrete|Ponte Internacion...|      36560|\n",
      "|26122645|[[-25.691851, -54...|[307420349, 16982...|    null|   null|   paved|Rodovia das Catar...|      36560|\n",
      "|26122801|[[-25.5637579, -5...|[5514364259, 5514...|    null|   null|    null| Rua Natal Graciotin|      36560|\n",
      "+--------+--------------------+--------------------+--------+-------+--------+--------------------+-----------+\n",
      "only showing top 5 rows\n",
      "\n",
      "root\n",
      " |-- lat: double (nullable = true)\n",
      " |-- lon: double (nullable = true)\n",
      " |-- id: long (nullable = true)\n",
      "\n",
      "+-----------+-----------+--------+\n",
      "|        lat|        lon|      id|\n",
      "+-----------+-----------+--------+\n",
      "|-25.5343718|-54.5761722|25835738|\n",
      "|-25.5343227|-54.5760531|25835738|\n",
      "|-25.5341375|-54.5756818|25835738|\n",
      "|-25.5337506|-54.5750471|25835738|\n",
      "|-25.5334046|-54.5746139|25835738|\n",
      "+-----------+-----------+--------+\n",
      "only showing top 5 rows\n",
      "\n",
      "+--------------------+--------+--------+-------+--------+--------------------+--------------------+-----------+\n",
      "|         coordinates|      id|maxspeed|incline| surface|                name|               nodes|total_nodes|\n",
      "+--------------------+--------+--------+-------+--------+--------------------+--------------------+-----------+\n",
      "|[-25.5343718,-54....|25835738|    null|   null|    null|Avenida Costa e S...|[362528326, 28597...|      36560|\n",
      "|[-25.5868602,-54....|26122619|      60|   null|   paved|    Avenida Mercosul|[285975617, 59202...|      36560|\n",
      "|[-25.5868602,-54....|26122631|      60|   null|concrete|Ponte Internacion...|[285975617, 69310...|      36560|\n",
      "|[-25.691851,-54.4...|26122645|    null|   null|   paved|Rodovia das Catar...|[307420349, 16982...|      36560|\n",
      "|[-25.5637579,-54....|26122801|    null|   null|    null| Rua Natal Graciotin|[5514364259, 5514...|      36560|\n",
      "+--------------------+--------+--------+-------+--------+--------------------+--------------------+-----------+\n",
      "only showing top 5 rows\n",
      "\n",
      "+--------------------+--------+--------+-------+--------+--------------------+--------------------+-----------+\n",
      "|                geom|      id|maxspeed|incline| surface|                name|               nodes|total_nodes|\n",
      "+--------------------+--------+--------+-------+--------+--------------------+--------------------+-----------+\n",
      "|LINESTRING (-25.5...|25835738|    null|   null|    null|Avenida Costa e S...|[362528326, 28597...|      36560|\n",
      "|LINESTRING (-25.5...|26122619|      60|   null|   paved|    Avenida Mercosul|[285975617, 59202...|      36560|\n",
      "|LINESTRING (-25.5...|26122631|      60|   null|concrete|Ponte Internacion...|[285975617, 69310...|      36560|\n",
      "|LINESTRING (-25.6...|26122645|    null|   null|   paved|Rodovia das Catar...|[307420349, 16982...|      36560|\n",
      "|LINESTRING (-25.5...|26122801|    null|   null|    null| Rua Natal Graciotin|[5514364259, 5514...|      36560|\n",
      "+--------------------+--------+--------+-------+--------+--------------------+--------------------+-----------+\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql.functions import explode, arrays_zip\n",
    "\n",
    "df.createOrReplaceTempView(\"df\")\n",
    "tb = spark.sql(\"select *, size(elements) total_nodes from df\")\n",
    "tb.show(5)\n",
    "\n",
    "isolate_total_nodes = tb.select(\"total_nodes\").toPandas()\n",
    "total_nodes = isolate_total_nodes[\"total_nodes\"].iloc[0]\n",
    "print(total_nodes)\n",
    "\n",
    "isolate_ids = tb.select(\"elements.id\").toPandas()\n",
    "ids = pd.DataFrame(isolate_ids[\"id\"].iloc[0]).drop_duplicates()\n",
    "print(ids[0].iloc[1])\n",
    "\n",
    "formatted_df = tb.withColumn(\"id\", explode(\"elements.id\"))\n",
    "\n",
    "formatted_df.show(5)\n",
    "\n",
    "formatted_df = tb.withColumn(\n",
    "    \"new\",\n",
    "    arrays_zip(\"elements.id\", \"elements.geometry\", \"elements.nodes\", \"elements.tags\"),\n",
    ").withColumn(\"new\", explode(\"new\"))\n",
    "\n",
    "formatted_df.show(5)\n",
    "\n",
    "# formatted_df.printSchema()\n",
    "\n",
    "formatted_df = formatted_df.select(\n",
    "    \"new.0\",\n",
    "    \"new.1\",\n",
    "    \"new.2\",\n",
    "    \"new.3.maxspeed\",\n",
    "    \"new.3.incline\",\n",
    "    \"new.3.surface\",\n",
    "    \"new.3.name\",\n",
    "    \"total_nodes\",\n",
    ")\n",
    "formatted_df = (\n",
    "    formatted_df.withColumnRenamed(\"0\", \"id\")\n",
    "    .withColumnRenamed(\"1\", \"geom\")\n",
    "    .withColumnRenamed(\"2\", \"nodes\")\n",
    "    .withColumnRenamed(\"3\", \"tags\")\n",
    ")\n",
    "formatted_df.createOrReplaceTempView(\"formatted_df\")\n",
    "formatted_df.show(5)\n",
    "# TODO atualizar daqui para baixo para considerar a linha inteira na lógica\n",
    "points_tb = spark.sql(\"select geom, id from formatted_df where geom IS NOT NULL\")\n",
    "points_tb = points_tb.withColumn(\"new\", arrays_zip(\"geom.lat\", \"geom.lon\")).withColumn(\n",
    "    \"new\", explode(\"new\")\n",
    ")\n",
    "\n",
    "points_tb = points_tb.select(\"new.0\", \"new.1\", \"id\")\n",
    "\n",
    "points_tb = points_tb.withColumnRenamed(\"0\", \"lat\").withColumnRenamed(\"1\", \"lon\")\n",
    "points_tb.printSchema()\n",
    "\n",
    "points_tb.createOrReplaceTempView(\"points_tb\")\n",
    "\n",
    "points_tb.show(5)\n",
    "\n",
    "# teste = spark.sql(\"select st_point(lat, lon) as geom, id from points_tb\")\n",
    "\n",
    "# teste = spark.sql(\"select collect_list(array(p1.lat,p1.lon)) as line from points_tb p1 where p1.id = 25835738 group by p1.id\")\n",
    "# teste.show(5)\n",
    "# print(teste.take(1))\n",
    "\n",
    "# SELECT ST_AsText(ST_Envelope(\n",
    "# \t\tST_Collect(\n",
    "# \t\t\tST_GeomFromText('LINESTRING(55 75,125 150)'),\n",
    "# \t\t\t\tST_Point(20, 80))\n",
    "# \t\t\t\t)) As wktenv;\n",
    "\n",
    "coordinates_tb = spark.sql(\n",
    "    \"select (select collect_list(CONCAT(p1.lat,',',p1.lon)) from points_tb p1 where p1.id = p2.id group by p1.id) as coordinates, p2.id, p2.maxspeed, p2.incline, p2.surface, p2.name, p2.nodes, p2.total_nodes from formatted_df p2\"\n",
    ")\n",
    "coordinates_tb.createOrReplaceTempView(\"coordinates_tb\")\n",
    "coordinates_tb.show(5)\n",
    "\n",
    "roads_tb = spark.sql(\n",
    "    \"SELECT ST_LineStringFromText(REPLACE(REPLACE(CAST(coordinates as string),'[',''),']',''), ',') as geom, id, maxspeed, incline, surface, name, nodes, total_nodes FROM coordinates_tb WHERE coordinates IS NOT NULL\"\n",
    ")\n",
    "roads_tb.createOrReplaceTempView(\"roads_tb\")\n",
    "roads_tb.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f7cf4991",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[Row(geom=<shapely.geometry.linestring.LineString object at 0x7f8b6c4fefd0>)]\n"
     ]
    }
   ],
   "source": [
    "print(roads_tb.select(\"geom\").take(1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "4c6d1dcd",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+-------------------+-------------------+------------------+--------------------+--------+--------+-------+--------+--------------------+-----------+\n",
      "|               nodes|     distance_toend|           distance|          geomsize|                geom|      id|maxspeed|incline| surface|                name|total_nodes|\n",
      "+--------------------+-------------------+-------------------+------------------+--------------------+--------+--------+-------+--------+--------------------+-----------+\n",
      "|[362528326, 28597...|0.04960428437877076|0.06191383729684203|11.818939546233448|LINESTRING (-25.5...|25835738|    null|   null|    null|Avenida Costa e S...|      36560|\n",
      "|[285975617, 59202...| 0.1036513492683037|0.11504041822963577|  8.00531493113018|LINESTRING (-25.5...|26122619|      60|   null|   paved|    Avenida Mercosul|      36560|\n",
      "|[285975617, 69310...|0.10941553855472455|0.12057253982300521|1.9538901610218935|LINESTRING (-25.5...|26122631|      60|   null|concrete|Ponte Internacion...|      36560|\n",
      "|[307420349, 16982...|0.24791409977758808| 0.2619862588434014|11.873439986567648|LINESTRING (-25.6...|26122645|    null|   null|   paved|Rodovia das Catar...|      36560|\n",
      "|[5514364259, 5514...|  0.079155366559698|0.08873043404159503| 6.558866347858073|LINESTRING (-25.5...|26122801|    null|   null|    null| Rua Natal Graciotin|      36560|\n",
      "+--------------------+-------------------+-------------------+------------------+--------------------+--------+--------+-------+--------+--------------------+-----------+\n",
      "only showing top 5 rows\n",
      "\n",
      "+--------------------+--------+-------+--------+--------------------+--------+--------------------+------------------+-------------------+-------------------+-----------+\n",
      "|               nodes|maxspeed|incline| surface|                name|      id|                geom|          geomsize|           distance|     distance_toend|total_nodes|\n",
      "+--------------------+--------+-------+--------+--------------------+--------+--------------------+------------------+-------------------+-------------------+-----------+\n",
      "|[362528326, 28597...|      20|     0%|    soil|Avenida Costa e S...|25835738|LINESTRING (-25.5...|11.818939546233448|0.06191383729684203|0.04960428437877076|      36560|\n",
      "|[285975617, 59202...|      60|     0%|   paved|    Avenida Mercosul|26122619|LINESTRING (-25.5...|  8.00531493113018|0.11504041822963577| 0.1036513492683037|      36560|\n",
      "|[285975617, 69310...|      60|     0%|concrete|Ponte Internacion...|26122631|LINESTRING (-25.5...|1.9538901610218935|0.12057253982300521|0.10941553855472455|      36560|\n",
      "|[307420349, 16982...|      20|     0%|   paved|Rodovia das Catar...|26122645|LINESTRING (-25.6...|11.873439986567648| 0.2619862588434014|0.24791409977758808|      36560|\n",
      "|[5514364259, 5514...|      20|     0%|    soil| Rua Natal Graciotin|26122801|LINESTRING (-25.5...| 6.558866347858073|0.08873043404159503|  0.079155366559698|      36560|\n",
      "+--------------------+--------+-------+--------+--------------------+--------+--------------------+------------------+-------------------+-------------------+-----------+\n",
      "only showing top 5 rows\n",
      "\n",
      "+--------------------+-------------+--------+-------+--------+--------------------+--------+--------------------+------------------+-------------------+-------------------+-----------+\n",
      "|               nodes|surface_index|maxspeed|incline| surface|                name|      id|                geom|          geomsize|           distance|     distance_toend|total_nodes|\n",
      "+--------------------+-------------+--------+-------+--------+--------------------+--------+--------------------+------------------+-------------------+-------------------+-----------+\n",
      "|[362528326, 28597...|         0.04|      20|     0%|    soil|Avenida Costa e S...|25835738|LINESTRING (-25.5...|11.818939546233448|0.06191383729684203|0.04960428437877076|      36560|\n",
      "|[285975617, 59202...|         0.03|      60|     0%|   paved|    Avenida Mercosul|26122619|LINESTRING (-25.5...|  8.00531493113018|0.11504041822963577| 0.1036513492683037|      36560|\n",
      "|[285975617, 69310...|         0.02|      60|     0%|concrete|Ponte Internacion...|26122631|LINESTRING (-25.5...|1.9538901610218935|0.12057253982300521|0.10941553855472455|      36560|\n",
      "|[307420349, 16982...|         0.03|      20|     0%|   paved|Rodovia das Catar...|26122645|LINESTRING (-25.6...|11.873439986567648| 0.2619862588434014|0.24791409977758808|      36560|\n",
      "|[5514364259, 5514...|         0.04|      20|     0%|    soil| Rua Natal Graciotin|26122801|LINESTRING (-25.5...| 6.558866347858073|0.08873043404159503|  0.079155366559698|      36560|\n",
      "+--------------------+-------------+--------+-------+--------+--------------------+--------+--------------------+------------------+-------------------+-------------------+-----------+\n",
      "only showing top 5 rows\n",
      "\n",
      "+--------------------+-------------+-------------+--------+-------+--------+--------------------+--------+--------------------+------------------+-------------------+-------------------+-----------+\n",
      "|               nodes|incline_index|surface_index|maxspeed|incline| surface|                name|      id|                geom|          geomsize|           distance|     distance_toend|total_nodes|\n",
      "+--------------------+-------------+-------------+--------+-------+--------+--------------------+--------+--------------------+------------------+-------------------+-------------------+-----------+\n",
      "|[362528326, 28597...|         0.00|         0.04|      20|     0%|    soil|Avenida Costa e S...|25835738|LINESTRING (-25.5...|11.818939546233448|0.06191383729684203|0.04960428437877076|      36560|\n",
      "|[285975617, 59202...|         0.00|         0.03|      60|     0%|   paved|    Avenida Mercosul|26122619|LINESTRING (-25.5...|  8.00531493113018|0.11504041822963577| 0.1036513492683037|      36560|\n",
      "|[285975617, 69310...|         0.00|         0.02|      60|     0%|concrete|Ponte Internacion...|26122631|LINESTRING (-25.5...|1.9538901610218935|0.12057253982300521|0.10941553855472455|      36560|\n",
      "|[307420349, 16982...|         0.00|         0.03|      20|     0%|   paved|Rodovia das Catar...|26122645|LINESTRING (-25.6...|11.873439986567648| 0.2619862588434014|0.24791409977758808|      36560|\n",
      "|[5514364259, 5514...|         0.00|         0.04|      20|     0%|    soil| Rua Natal Graciotin|26122801|LINESTRING (-25.5...| 6.558866347858073|0.08873043404159503|  0.079155366559698|      36560|\n",
      "+--------------------+-------------+-------------+--------+-------+--------+--------------------+--------+--------------------+------------------+-------------------+-------------------+-----------+\n",
      "only showing top 5 rows\n",
      "\n",
      "+--------------------+------+-------------+-------------+--------+-------+--------+--------------------+--------+--------------------+------------------+-------------------+-------------------+-----------+\n",
      "|               nodes|weight|incline_index|surface_index|maxspeed|incline| surface|                name|      id|                geom|          geomsize|           distance|     distance_toend|total_nodes|\n",
      "+--------------------+------+-------------+-------------+--------+-------+--------+--------------------+--------+--------------------+------------------+-------------------+-------------------+-----------+\n",
      "|[362528326, 28597...|  39.2|         0.00|         0.04|      20|     0%|    soil|Avenida Costa e S...|25835738|LINESTRING (-25.5...|11.818939546233448|0.06191383729684203|0.04960428437877076|      36560|\n",
      "|[285975617, 59202...| 118.2|         0.00|         0.03|      60|     0%|   paved|    Avenida Mercosul|26122619|LINESTRING (-25.5...|  8.00531493113018|0.11504041822963577| 0.1036513492683037|      36560|\n",
      "|[285975617, 69310...| 118.8|         0.00|         0.02|      60|     0%|concrete|Ponte Internacion...|26122631|LINESTRING (-25.5...|1.9538901610218935|0.12057253982300521|0.10941553855472455|      36560|\n",
      "|[307420349, 16982...|  39.4|         0.00|         0.03|      20|     0%|   paved|Rodovia das Catar...|26122645|LINESTRING (-25.6...|11.873439986567648| 0.2619862588434014|0.24791409977758808|      36560|\n",
      "|[5514364259, 5514...|  39.2|         0.00|         0.04|      20|     0%|    soil| Rua Natal Graciotin|26122801|LINESTRING (-25.5...| 6.558866347858073|0.08873043404159503|  0.079155366559698|      36560|\n",
      "+--------------------+------+-------------+-------------+--------+-------+--------+--------------------+--------+--------------------+------------------+-------------------+-------------------+-----------+\n"
     ]
    }
   ],
   "source": [
    "# Não foi considerado que um caminha pode necessitar mais de 1 rua\n",
    "\n",
    "start_point = \"-25.4695946,-54.5909028\"\n",
    "end_point = \"-25.4786993,-54.57938\"\n",
    "\n",
    "distance_tb = spark.sql(\n",
    "    \"select nodes, st_distance(geom, st_point(\"\n",
    "    + end_point\n",
    "    + \")) as distance_toend, st_distance(st_point(\"\n",
    "    + start_point\n",
    "    + \"), geom) as distance, st_length(geom) * 1000 as geomsize, geom, id, maxspeed, incline, surface, name , total_nodes from roads_tb\"\n",
    ")\n",
    "distance_tb.createOrReplaceTempView(\"distance_tb\")\n",
    "distance_tb.show(5)\n",
    "\n",
    "# considerar distância, direcao(ex: 0 180 e etc), inclinacao(up, down, 0%), superficie(asphalt,paved, concrete), velocidade(60 80 50 40 e etc)\n",
    "fill_null_tb = spark.sql(\n",
    "    \"select nodes, IFNULL(maxspeed, 20) as maxspeed, IFNULL(incline, '0%') as incline, IFNULL(surface, 'soil') as surface, name, id, geom, geomsize, distance, distance_toend, total_nodes  from distance_tb\"\n",
    ")\n",
    "fill_null_tb.createOrReplaceTempView(\"fill_null_tb\")\n",
    "fill_null_tb.show(5)\n",
    "\n",
    "surface_index_tb = spark.sql(\n",
    "    \"select nodes, case surface when 'asphalt'\"\n",
    "    + \"then 0.01 when 'concrete'\"\n",
    "    + \"then 0.02 when 'paved'\"\n",
    "    + \"then 0.03 when 'soil'\"\n",
    "    + \"then 0.04 when 'unpaved'\"\n",
    "    + \"then 0.04 when 'sett'\"\n",
    "    + \"then 0.03 ELSE 0.05 end as surface_index,\"\n",
    "    + \"maxspeed, incline, surface, name, id, geom, geomsize, distance, distance_toend, total_nodes  from fill_null_tb\"\n",
    ")\n",
    "surface_index_tb.createOrReplaceTempView(\"surface_index_tb\")\n",
    "surface_index_tb.show(5)\n",
    "\n",
    "incline_index_tb = spark.sql(\n",
    "    \"select nodes, case incline when 'top' then -0.10 when 'down' then 0.10 when '0%' then 0 end as incline_index, surface_index, maxspeed, incline, surface, name, id, geom, geomsize, distance, distance_toend, total_nodes  from surface_index_tb\"\n",
    ")\n",
    "incline_index_tb.createOrReplaceTempView(\"incline_index_tb\")\n",
    "incline_index_tb.show(5)\n",
    "\n",
    "weight_index_tb = spark.sql(\n",
    "    \"select nodes, (maxspeed - (maxspeed * surface_index)) + (maxspeed +(maxspeed * incline_index)) as weight, incline_index, surface_index, maxspeed, incline, surface, name, id, geom, geomsize, distance, distance_toend, total_nodes  from incline_index_tb WHERE geomsize IS NOT NULL\"\n",
    ")\n",
    "weight_index_tb.createOrReplaceTempView(\"weight_index_tb\")\n",
    "weight_index_tb.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "6699c6b5",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+\n",
      "|min(weight)|\n",
      "+-----------+\n",
      "|       39.0|\n",
      "+-----------+\n"
     ]
    }
   ],
   "source": [
    "teste = spark.sql(\"select min(weight) from weight_index_tb\")\n",
    "teste.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "821ea4ea",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "32344883\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "22/02/01 12:44:21 ERROR Utils: Uncaught exception in thread element-tracking-store-worker\n",
      "java.util.NoSuchElementException: key not found: 4363\n",
      "\tat scala.collection.MapLike.default(MapLike.scala:235)\n",
      "\tat scala.collection.MapLike.default$(MapLike.scala:234)\n",
      "\tat scala.collection.AbstractMap.default(Map.scala:63)\n",
      "\tat scala.collection.MapLike.apply(MapLike.scala:144)\n",
      "\tat scala.collection.MapLike.apply$(MapLike.scala:143)\n",
      "\tat scala.collection.AbstractMap.apply(Map.scala:63)\n",
      "\tat org.apache.spark.sql.execution.ui.SQLAppStatusListener.$anonfun$aggregateMetrics$11(SQLAppStatusListener.scala:257)\n",
      "\tat scala.collection.TraversableLike.$anonfun$map$1(TraversableLike.scala:238)\n",
      "\tat scala.collection.mutable.HashMap.$anonfun$foreach$1(HashMap.scala:149)\n",
      "\tat scala.collection.mutable.HashTable.foreachEntry(HashTable.scala:237)\n",
      "\tat scala.collection.mutable.HashTable.foreachEntry$(HashTable.scala:230)\n",
      "\tat scala.collection.mutable.HashMap.foreachEntry(HashMap.scala:44)\n",
      "\tat scala.collection.mutable.HashMap.foreach(HashMap.scala:149)\n",
      "\tat scala.collection.TraversableLike.map(TraversableLike.scala:238)\n",
      "\tat scala.collection.TraversableLike.map$(TraversableLike.scala:231)\n",
      "\tat scala.collection.AbstractTraversable.map(Traversable.scala:108)\n",
      "\tat org.apache.spark.sql.execution.ui.SQLAppStatusListener.aggregateMetrics(SQLAppStatusListener.scala:256)\n",
      "\tat org.apache.spark.sql.execution.ui.SQLAppStatusListener.$anonfun$onExecutionEnd$2(SQLAppStatusListener.scala:365)\n",
      "\tat scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23)\n",
      "\tat org.apache.spark.util.Utils$.tryLog(Utils.scala:1945)\n",
      "\tat org.apache.spark.status.ElementTrackingStore$$anon$1.run(ElementTrackingStore.scala:117)\n",
      "\tat java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)\n",
      "\tat java.util.concurrent.FutureTask.run(FutureTask.java:266)\n",
      "\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n",
      "\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n",
      "\tat java.lang.Thread.run(Thread.java:748)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+--------------------+\n",
      "|      id|            distance|\n",
      "+--------+--------------------+\n",
      "|32347167|8.330276767022637E-5|\n",
      "+--------+--------------------+\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "22/02/01 12:44:22 ERROR Utils: Uncaught exception in thread element-tracking-store-worker\n",
      "java.util.NoSuchElementException: key not found: 5357\n",
      "\tat scala.collection.MapLike.default(MapLike.scala:235)\n",
      "\tat scala.collection.MapLike.default$(MapLike.scala:234)\n",
      "\tat scala.collection.AbstractMap.default(Map.scala:63)\n",
      "\tat scala.collection.MapLike.apply(MapLike.scala:144)\n",
      "\tat scala.collection.MapLike.apply$(MapLike.scala:143)\n",
      "\tat scala.collection.AbstractMap.apply(Map.scala:63)\n",
      "\tat org.apache.spark.sql.execution.ui.SQLAppStatusListener.$anonfun$aggregateMetrics$11(SQLAppStatusListener.scala:257)\n",
      "\tat scala.collection.TraversableLike.$anonfun$map$1(TraversableLike.scala:238)\n",
      "\tat scala.collection.mutable.HashMap.$anonfun$foreach$1(HashMap.scala:149)\n",
      "\tat scala.collection.mutable.HashTable.foreachEntry(HashTable.scala:237)\n",
      "\tat scala.collection.mutable.HashTable.foreachEntry$(HashTable.scala:230)\n",
      "\tat scala.collection.mutable.HashMap.foreachEntry(HashMap.scala:44)\n",
      "\tat scala.collection.mutable.HashMap.foreach(HashMap.scala:149)\n",
      "\tat scala.collection.TraversableLike.map(TraversableLike.scala:238)\n",
      "\tat scala.collection.TraversableLike.map$(TraversableLike.scala:231)\n",
      "\tat scala.collection.AbstractTraversable.map(Traversable.scala:108)\n",
      "\tat org.apache.spark.sql.execution.ui.SQLAppStatusListener.aggregateMetrics(SQLAppStatusListener.scala:256)\n",
      "\tat org.apache.spark.sql.execution.ui.SQLAppStatusListener.$anonfun$onExecutionEnd$2(SQLAppStatusListener.scala:365)\n",
      "\tat scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23)\n",
      "\tat org.apache.spark.util.Utils$.tryLog(Utils.scala:1945)\n",
      "\tat org.apache.spark.status.ElementTrackingStore$$anon$1.run(ElementTrackingStore.scala:117)\n",
      "\tat java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)\n",
      "\tat java.util.concurrent.FutureTask.run(FutureTask.java:266)\n",
      "\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n",
      "\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n",
      "\tat java.lang.Thread.run(Thread.java:748)\n",
      "22/02/01 12:44:23 ERROR Utils: Uncaught exception in thread element-tracking-store-worker\n",
      "java.util.NoSuchElementException: key not found: 6462\n",
      "\tat scala.collection.MapLike.default(MapLike.scala:235)\n",
      "\tat scala.collection.MapLike.default$(MapLike.scala:234)\n",
      "\tat scala.collection.AbstractMap.default(Map.scala:63)\n",
      "\tat scala.collection.MapLike.apply(MapLike.scala:144)\n",
      "\tat scala.collection.MapLike.apply$(MapLike.scala:143)\n",
      "\tat scala.collection.AbstractMap.apply(Map.scala:63)\n",
      "\tat org.apache.spark.sql.execution.ui.SQLAppStatusListener.$anonfun$aggregateMetrics$11(SQLAppStatusListener.scala:257)\n",
      "\tat scala.collection.TraversableLike.$anonfun$map$1(TraversableLike.scala:238)\n",
      "\tat scala.collection.mutable.HashMap.$anonfun$foreach$1(HashMap.scala:149)\n",
      "\tat scala.collection.mutable.HashTable.foreachEntry(HashTable.scala:237)\n",
      "\tat scala.collection.mutable.HashTable.foreachEntry$(HashTable.scala:230)\n",
      "\tat scala.collection.mutable.HashMap.foreachEntry(HashMap.scala:44)\n",
      "\tat scala.collection.mutable.HashMap.foreach(HashMap.scala:149)\n",
      "\tat scala.collection.TraversableLike.map(TraversableLike.scala:238)\n",
      "\tat scala.collection.TraversableLike.map$(TraversableLike.scala:231)\n",
      "\tat scala.collection.AbstractTraversable.map(Traversable.scala:108)\n",
      "\tat org.apache.spark.sql.execution.ui.SQLAppStatusListener.aggregateMetrics(SQLAppStatusListener.scala:256)\n",
      "\tat org.apache.spark.sql.execution.ui.SQLAppStatusListener.$anonfun$onExecutionEnd$2(SQLAppStatusListener.scala:365)\n",
      "\tat scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23)\n",
      "\tat org.apache.spark.util.Utils$.tryLog(Utils.scala:1945)\n",
      "\tat org.apache.spark.status.ElementTrackingStore$$anon$1.run(ElementTrackingStore.scala:117)\n",
      "\tat java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)\n",
      "\tat java.util.concurrent.FutureTask.run(FutureTask.java:266)\n",
      "\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n",
      "\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n",
      "\tat java.lang.Thread.run(Thread.java:748)\n",
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+\n",
      "|            boundary|\n",
      "+--------------------+\n",
      "|POLYGON ((-25.479...|\n",
      "+--------------------+\n",
      "\n",
      "+--------+--------+\n",
      "|contains|      id|\n",
      "+--------+--------+\n",
      "|   false|25835738|\n",
      "|   false|26122619|\n",
      "|   false|26122631|\n",
      "|   false|26122645|\n",
      "|   false|26122801|\n",
      "+--------+--------+\n",
      "only showing top 5 rows\n",
      "\n",
      "+---------+--------------------+\n",
      "|       id|                geom|\n",
      "+---------+--------------------+\n",
      "| 32344891|LINESTRING (-25.4...|\n",
      "|197591238|LINESTRING (-25.4...|\n",
      "|918520467|LINESTRING (-25.4...|\n",
      "| 32345349|LINESTRING (-25.4...|\n",
      "|964560305|LINESTRING (-25.4...|\n",
      "+---------+--------------------+\n"
     ]
    }
   ],
   "source": [
    "closestoend_tb = spark.sql(\n",
    "    \"select w1.id, w1.distance_toend from weight_index_tb w1 group by w1.id, w1.distance_toend having (select min(w2.distance_toend) as distance_toend  from weight_index_tb w2) = w1.distance_toend\"\n",
    ")\n",
    "closestoend_tb.createOrReplaceTempView(\"closestoend_tb\")\n",
    "closestoend = closestoend_tb.take(1)[0][\"id\"]\n",
    "print(closestoend)\n",
    "\n",
    "closestostart_tb = spark.sql(\n",
    "    \"select w1.id, w1.distance from weight_index_tb w1 group by w1.id, w1.distance having (select min(w2.distance) as distance  from weight_index_tb w2) = w1.distance\"\n",
    ")\n",
    "closestostart_tb.createOrReplaceTempView(\"closestostart_tb\")\n",
    "closestostart_tb.show(5)\n",
    "\n",
    "closestostart = closestostart_tb.take(1)[0][\"id\"]\n",
    "\n",
    "# FOLIUM EM 3857 dado em 4326 st_transform(st_union_aggr(geom),'epsg:3857','epsg:4326')\n",
    "json_lines = spark.sql(\n",
    "    \"select ST_AsGeoJSON(st_envelope_aggr(geom)) AS json  from weight_index_tb where id in (\"\n",
    "    + str(closestostart)\n",
    "    + \",\"\n",
    "    + str(closestoend)\n",
    "    + \")\"\n",
    ")\n",
    "json_lines_string_teste = json_lines.take(1)[0][\"json\"]\n",
    "coordinates_teste = json.loads(json_lines_string_teste)[\"coordinates\"]\n",
    "\n",
    "\n",
    "# st_boundary st_contains\n",
    "\n",
    "# Pegar o limite entre a uniao da geom inicial e final\n",
    "# select st_boundary(st_union_aggr(geom)) AS boundary  from weight_index_tb where id in (\"+str(closestostart)+\",\"+str(closestoend)+\")\n",
    "\n",
    "boundary_tb = spark.sql(\n",
    "    \"select st_envelope_aggr(geom) as boundary from weight_index_tb where id in (\"\n",
    "    + str(closestostart)\n",
    "    + \",\"\n",
    "    + str(closestoend)\n",
    "    + \")\"\n",
    ")\n",
    "boundary_tb.createOrReplaceTempView(\"boundary_tb\")\n",
    "boundary_tb.show(5)\n",
    "\n",
    "contains_tb = spark.sql(\n",
    "    \"select st_intersects(boundary,geom) as contains, id from weight_index_tb, boundary_tb\"\n",
    ")\n",
    "contains_tb.createOrReplaceTempView(\"contains_tb\")\n",
    "contains_tb.show(5)\n",
    "\n",
    "possible_paths = spark.sql(\n",
    "    \"select id, geom from weight_index_tb group by id, geom having id in (select id from contains_tb where contains = true)\"\n",
    ")\n",
    "possible_paths.createOrReplaceTempView(\"possible_paths\")\n",
    "possible_paths.show(5)\n",
    "\n",
    "paths_collection = spark.sql(\n",
    "    \"select ST_AsGeoJSON(st_union_aggr(geom)) AS json from possible_paths\"\n",
    ")\n",
    "json_lines_string = paths_collection.take(1)[0][\"json\"]\n",
    "coordinates = json.loads(json_lines_string)[\"coordinates\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "71007b9b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+\n",
      "|     distance_toend|\n",
      "+-------------------+\n",
      "|0.01362215583892774|\n",
      "+-------------------+\n",
      "\n",
      "select geom, id, weight from touches_tb where touches = true and distance_toend < 0.01362215583892774 and distance > 8.330276767022637e-05 and id NOT IN (32347167)\n",
      "+--------------------+---------+------+\n",
      "|                geom|       id|weight|\n",
      "+--------------------+---------+------+\n",
      "|LINESTRING (-25.4...| 32347009|  39.2|\n",
      "|LINESTRING (-25.4...|197591238|  39.2|\n",
      "+--------------------+---------+------+\n",
      "\n",
      "+---------+--------------------+-----------+\n",
      "|       id|                geom|max(weight)|\n",
      "+---------+--------------------+-----------+\n",
      "|197591238|LINESTRING (-25.4...|       39.2|\n",
      "| 32347009|LINESTRING (-25.4...|       39.2|\n",
      "+---------+--------------------+-----------+\n",
      "\n",
      "+--------------------+\n",
      "|      distance_toend|\n",
      "+--------------------+\n",
      "|0.011868762484771467|\n",
      "+--------------------+\n",
      "\n",
      "select geom, id, weight from touches_tb where touches = true and distance_toend < 0.011868762484771467 and distance > 0.001627280815937359 and id NOT IN (32347167, 197591238)\n",
      "+--------------------+---------+------+\n",
      "|                geom|       id|weight|\n",
      "+--------------------+---------+------+\n",
      "|LINESTRING (-25.4...|197591235|  39.2|\n",
      "+--------------------+---------+------+\n",
      "\n",
      "+---------+--------------------+-----------+\n",
      "|       id|                geom|max(weight)|\n",
      "+---------+--------------------+-----------+\n",
      "|197591235|LINESTRING (-25.4...|       39.2|\n",
      "+---------+--------------------+-----------+\n",
      "\n",
      "+--------------------+\n",
      "|      distance_toend|\n",
      "+--------------------+\n",
      "|0.010329729036617774|\n",
      "+--------------------+\n",
      "\n",
      "select geom, id, weight from touches_tb where touches = true and distance_toend < 0.010329729036617774 and distance > 0.0018402611895601347 and id NOT IN (32347167, 197591238, 197591235)\n",
      "+--------------------+---------+------+\n",
      "|                geom|       id|weight|\n",
      "+--------------------+---------+------+\n",
      "|LINESTRING (-25.4...|197591232|  39.2|\n",
      "+--------------------+---------+------+\n",
      "\n",
      "+---------+--------------------+-----------+\n",
      "|       id|                geom|max(weight)|\n",
      "+---------+--------------------+-----------+\n",
      "|197591232|LINESTRING (-25.4...|       39.2|\n",
      "+---------+--------------------+-----------+\n",
      "\n",
      "+--------------------+\n",
      "|      distance_toend|\n",
      "+--------------------+\n",
      "|0.007666208743570536|\n",
      "+--------------------+\n",
      "\n",
      "select geom, id, weight from touches_tb where touches = true and distance_toend < 0.007666208743570536 and distance > 0.004980378367153946 and id NOT IN (32347167, 197591238, 197591235, 197591232)\n",
      "+--------------------+---------+------+\n",
      "|                geom|       id|weight|\n",
      "+--------------------+---------+------+\n",
      "|LINESTRING (-25.4...|437818123| 117.6|\n",
      "+--------------------+---------+------+\n",
      "\n",
      "+---------+--------------------+-----------+\n",
      "|       id|                geom|max(weight)|\n",
      "+---------+--------------------+-----------+\n",
      "|437818123|LINESTRING (-25.4...|      117.6|\n",
      "+---------+--------------------+-----------+\n",
      "\n",
      "+--------------------+\n",
      "|      distance_toend|\n",
      "+--------------------+\n",
      "|0.004314646750311...|\n",
      "+--------------------+\n",
      "\n",
      "select geom, id, weight from touches_tb where touches = true and distance_toend < 0.0043146467503117755 and distance > 0.005667965772492608 and id NOT IN (32347167, 197591238, 197591235, 197591232, 437818123)\n",
      "+--------------------+---------+------+\n",
      "|                geom|       id|weight|\n",
      "+--------------------+---------+------+\n",
      "|LINESTRING (-25.4...|933800237| 117.6|\n",
      "+--------------------+---------+------+\n",
      "\n",
      "+---------+--------------------+-----------+\n",
      "|       id|                geom|max(weight)|\n",
      "+---------+--------------------+-----------+\n",
      "|933800237|LINESTRING (-25.4...|      117.6|\n",
      "+---------+--------------------+-----------+\n",
      "\n",
      "+--------------------+\n",
      "|      distance_toend|\n",
      "+--------------------+\n",
      "|0.004249921855750084|\n",
      "+--------------------+\n",
      "\n",
      "select geom, id, weight from touches_tb where touches = true and distance_toend < 0.004249921855750084 and distance > 0.01157000312230015 and id NOT IN (32347167, 197591238, 197591235, 197591232, 437818123, 933800237)\n",
      "+--------------------+---------+------+\n",
      "|                geom|       id|weight|\n",
      "+--------------------+---------+------+\n",
      "|LINESTRING (-25.4...|933800236| 117.6|\n",
      "+--------------------+---------+------+\n",
      "\n",
      "+---------+--------------------+-----------+\n",
      "|       id|                geom|max(weight)|\n",
      "+---------+--------------------+-----------+\n",
      "|933800236|LINESTRING (-25.4...|      117.6|\n",
      "+---------+--------------------+-----------+\n",
      "\n",
      "+--------------------+\n",
      "|      distance_toend|\n",
      "+--------------------+\n",
      "|0.003896125505221...|\n",
      "+--------------------+\n",
      "\n",
      "select geom, id, weight from touches_tb where touches = true and distance_toend < 0.0038961255052215697 and distance > 0.011710186719691087 and id NOT IN (32347167, 197591238, 197591235, 197591232, 437818123, 933800237, 933800236)\n",
      "+----+---+------+\n",
      "|geom| id|weight|\n",
      "+----+---+------+\n",
      "+----+---+------+\n",
      "\n",
      "+---+----+-----------+\n",
      "| id|geom|max(weight)|\n",
      "+---+----+-----------+\n",
      "+---+----+-----------+\n",
      "\n",
      "(32347167, 197591238, 197591235, 197591232, 437818123, 933800237, 933800236, 32344883)\n"
     ]
    }
   ],
   "source": [
    "path = [closestostart]\n",
    "visited = [closestostart]\n",
    "current_nodes = spark.sql(\n",
    "    \"select geom from weight_index_tb where id = \" + str(closestostart)\n",
    ")\n",
    "row = current_nodes.rdd.collect()[0][\"geom\"]\n",
    "id_current = closestostart\n",
    "\n",
    "\n",
    "def choose_path(row, path, id_current, visited, copy_row):\n",
    "\n",
    "    visited_frm = str(visited).replace(\"[\", \"(\").replace(\"]\", \")\")\n",
    "\n",
    "    touches_tb = spark.sql(\n",
    "        \"select st_touches(st_geomfromwkt('\"\n",
    "        + str(row)\n",
    "        + \"'),geom) as touches, * from weight_index_tb where geom IS NOT NULL and distance_toend IS NOT NULL\"\n",
    "    )\n",
    "    touches_tb.createOrReplaceTempView(\"touches_tb\")\n",
    "    # st_distance(st_geomfromwkt('\"+str(row)+\"'),geom)\n",
    "    fim_distance = spark.sql(\n",
    "        \"select distance_toend from touches_tb where id = \" + str(id_current)\n",
    "    )\n",
    "    fim_distance.show(5)\n",
    "    fim_distance_value = fim_distance.rdd.collect()[0][\"distance_toend\"]\n",
    "\n",
    "    current_distance = spark.sql(\n",
    "        \"select distance from touches_tb where id = \" + str(id_current)\n",
    "    )\n",
    "    current_distance_value = current_distance.rdd.collect()[0][\"distance\"]\n",
    "\n",
    "    #     st_distance(st_geomfromwkt('\"+str(row)+\"'),geom) =\n",
    "\n",
    "    sql = (\n",
    "        \"select geom, id, weight from touches_tb where \"\n",
    "        + \"touches = true\"\n",
    "        + \" and \"\n",
    "        + \"distance_toend < \"\n",
    "        + str(fim_distance_value)\n",
    "        + \" and \"\n",
    "        + \"distance > \"\n",
    "        + str(current_distance_value)\n",
    "        + \" and \"\n",
    "        + \"id NOT IN \"\n",
    "        + visited_frm\n",
    "    )\n",
    "    print(sql)\n",
    "    current_nodes = spark.sql(sql)\n",
    "    current_nodes.createOrReplaceTempView(\"current_nodes\")\n",
    "    current_nodes.show(5)\n",
    "\n",
    "    current_node = spark.sql(\n",
    "        \"select id, geom, max(weight) from current_nodes group by id, geom, weight having max(weight) = weight \"\n",
    "    )\n",
    "    current_node.show(5)\n",
    "\n",
    "    if len(current_nodes.rdd.collect()) == 0:\n",
    "        return path\n",
    "    else:\n",
    "        row = current_node.rdd.collect()[0][\"geom\"]\n",
    "        id_current = current_node.rdd.collect()[0][\"id\"]\n",
    "        path.append(id_current)\n",
    "        visited.append(id_current)\n",
    "        return choose_path(row, path, id_current, visited, copy_row)\n",
    "\n",
    "\n",
    "path_ids = choose_path(row, path, id_current, visited, row)\n",
    "path_ids.append(closestoend)\n",
    "path_ids_frm = str(path_ids).replace(\"[\", \"(\").replace(\"]\", \")\")\n",
    "print(path_ids_frm)\n",
    "\n",
    "short_path = spark.sql(\n",
    "    \"select ST_AsGeoJSON(st_union_aggr(geom)) AS json from weight_index_tb where id in \"\n",
    "    + path_ids_frm\n",
    ")\n",
    "short_path_string = short_path.take(1)[0][\"json\"]\n",
    "short_path_coordinates = json.loads(short_path_string)[\"coordinates\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "7fbee596",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"width:100%;\"><div style=\"position:relative;width:100%;height:0;padding-bottom:60%;\"><span style=\"color:#565656\">Make this Notebook Trusted to load map: File -> Trust Notebook</span><iframe src=\"about:blank\" style=\"position:absolute;width:100%;height:100%;left:0;top:0;border:none !important;\" data-html=%3C%21DOCTYPE%20html%3E%0A%3Chead%3E%20%20%20%20%0A%20%20%20%20%3Cmeta%20http-equiv%3D%22content-type%22%20content%3D%22text/html%3B%20charset%3DUTF-8%22%20/%3E%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20%3Cscript%3E%0A%20%20%20%20%20%20%20%20%20%20%20%20L_NO_TOUCH%20%3D%20false%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20L_DISABLE_3D%20%3D%20false%3B%0A%20%20%20%20%20%20%20%20%3C/script%3E%0A%20%20%20%20%0A%20%20%20%20%3Cstyle%3Ehtml%2C%20body%20%7Bwidth%3A%20100%25%3Bheight%3A%20100%25%3Bmargin%3A%200%3Bpadding%3A%200%3B%7D%3C/style%3E%0A%20%20%20%20%3Cstyle%3E%23map%20%7Bposition%3Aabsolute%3Btop%3A0%3Bbottom%3A0%3Bright%3A0%3Bleft%3A0%3B%7D%3C/style%3E%0A%20%20%20%20%3Cscript%20src%3D%22https%3A//cdn.jsdelivr.net/npm/leaflet%401.6.0/dist/leaflet.js%22%3E%3C/script%3E%0A%20%20%20%20%3Cscript%20src%3D%22https%3A//code.jquery.com/jquery-1.12.4.min.js%22%3E%3C/script%3E%0A%20%20%20%20%3Cscript%20src%3D%22https%3A//maxcdn.bootstrapcdn.com/bootstrap/3.2.0/js/bootstrap.min.js%22%3E%3C/script%3E%0A%20%20%20%20%3Cscript%20src%3D%22https%3A//cdnjs.cloudflare.com/ajax/libs/Leaflet.awesome-markers/2.0.2/leaflet.awesome-markers.js%22%3E%3C/script%3E%0A%20%20%20%20%3Clink%20rel%3D%22stylesheet%22%20href%3D%22https%3A//cdn.jsdelivr.net/npm/leaflet%401.6.0/dist/leaflet.css%22/%3E%0A%20%20%20%20%3Clink%20rel%3D%22stylesheet%22%20href%3D%22https%3A//maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css%22/%3E%0A%20%20%20%20%3Clink%20rel%3D%22stylesheet%22%20href%3D%22https%3A//maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap-theme.min.css%22/%3E%0A%20%20%20%20%3Clink%20rel%3D%22stylesheet%22%20href%3D%22https%3A//maxcdn.bootstrapcdn.com/font-awesome/4.6.3/css/font-awesome.min.css%22/%3E%0A%20%20%20%20%3Clink%20rel%3D%22stylesheet%22%20href%3D%22https%3A//cdnjs.cloudflare.com/ajax/libs/Leaflet.awesome-markers/2.0.2/leaflet.awesome-markers.css%22/%3E%0A%20%20%20%20%3Clink%20rel%3D%22stylesheet%22%20href%3D%22https%3A//cdn.jsdelivr.net/gh/python-visualization/folium/folium/templates/leaflet.awesome.rotate.min.css%22/%3E%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%3Cmeta%20name%3D%22viewport%22%20content%3D%22width%3Ddevice-width%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20initial-scale%3D1.0%2C%20maximum-scale%3D1.0%2C%20user-scalable%3Dno%22%20/%3E%0A%20%20%20%20%20%20%20%20%20%20%20%20%3Cstyle%3E%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%23map_92f1cd33c95044be8d74b47a5ba7cf9c%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20position%3A%20relative%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20width%3A%20100.0%25%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20height%3A%20100.0%25%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20left%3A%200.0%25%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20top%3A%200.0%25%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20%3C/style%3E%0A%20%20%20%20%20%20%20%20%0A%3C/head%3E%0A%3Cbody%3E%20%20%20%20%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%3Cdiv%20class%3D%22folium-map%22%20id%3D%22map_92f1cd33c95044be8d74b47a5ba7cf9c%22%20%3E%3C/div%3E%0A%20%20%20%20%20%20%20%20%0A%3C/body%3E%0A%3Cscript%3E%20%20%20%20%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20var%20map_92f1cd33c95044be8d74b47a5ba7cf9c%20%3D%20L.map%28%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22map_92f1cd33c95044be8d74b47a5ba7cf9c%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20center%3A%20%5B-25.5172662%2C%20-54.6170038%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20crs%3A%20L.CRS.EPSG3857%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20zoom%3A%2012%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20zoomControl%3A%20true%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20preferCanvas%3A%20false%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20%29%3B%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%0A%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20var%20tile_layer_3819a206314f40e69da6b56c304d0e96%20%3D%20L.tileLayer%28%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22https%3A//%7Bs%7D.tile.openstreetmap.org/%7Bz%7D/%7Bx%7D/%7By%7D.png%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%22attribution%22%3A%20%22Data%20by%20%5Cu0026copy%3B%20%5Cu003ca%20href%3D%5C%22http%3A//openstreetmap.org%5C%22%5Cu003eOpenStreetMap%5Cu003c/a%5Cu003e%2C%20under%20%5Cu003ca%20href%3D%5C%22http%3A//www.openstreetmap.org/copyright%5C%22%5Cu003eODbL%5Cu003c/a%5Cu003e.%22%2C%20%22detectRetina%22%3A%20false%2C%20%22maxNativeZoom%22%3A%2018%2C%20%22maxZoom%22%3A%2018%2C%20%22minZoom%22%3A%200%2C%20%22noWrap%22%3A%20false%2C%20%22opacity%22%3A%201%2C%20%22subdomains%22%3A%20%22abc%22%2C%20%22tms%22%3A%20false%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20%29.addTo%28map_92f1cd33c95044be8d74b47a5ba7cf9c%29%3B%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20var%20marker_20573fda7687440c8652c51fa8e54a05%20%3D%20L.marker%28%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%5B-25.4695946%2C%20-54.5909028%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20%29.addTo%28map_92f1cd33c95044be8d74b47a5ba7cf9c%29%3B%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20var%20icon_c9218527075c4fb8b5356bca6fb9b09f%20%3D%20L.AwesomeMarkers.icon%28%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%22extraClasses%22%3A%20%22fa-rotate-0%22%2C%20%22icon%22%3A%20%22info-sign%22%2C%20%22iconColor%22%3A%20%22white%22%2C%20%22markerColor%22%3A%20%22green%22%2C%20%22prefix%22%3A%20%22glyphicon%22%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20%29%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20marker_20573fda7687440c8652c51fa8e54a05.setIcon%28icon_c9218527075c4fb8b5356bca6fb9b09f%29%3B%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20var%20popup_424de199ae3640218c76b34b3d9f3016%20%3D%20L.popup%28%7B%22maxWidth%22%3A%20%22100%25%22%7D%29%3B%0A%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20var%20html_6a8e6c52b1354c9e95602d87aeb76a6f%20%3D%20%24%28%60%3Cdiv%20id%3D%22html_6a8e6c52b1354c9e95602d87aeb76a6f%22%20style%3D%22width%3A%20100.0%25%3B%20height%3A%20100.0%25%3B%22%3E%3Ci%3EInicio%3C/i%3E%3C/div%3E%60%29%5B0%5D%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20popup_424de199ae3640218c76b34b3d9f3016.setContent%28html_6a8e6c52b1354c9e95602d87aeb76a6f%29%3B%0A%20%20%20%20%20%20%20%20%0A%0A%20%20%20%20%20%20%20%20marker_20573fda7687440c8652c51fa8e54a05.bindPopup%28popup_424de199ae3640218c76b34b3d9f3016%29%0A%20%20%20%20%20%20%20%20%3B%0A%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20marker_20573fda7687440c8652c51fa8e54a05.bindTooltip%28%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%60%3Cdiv%3E%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Click%20me%21%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%3C/div%3E%60%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%22sticky%22%3A%20true%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20%29%3B%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20var%20marker_ea3e5ee5e7fc480b993375b799c8af19%20%3D%20L.marker%28%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%5B-25.4786993%2C%20-54.57938%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20%29.addTo%28map_92f1cd33c95044be8d74b47a5ba7cf9c%29%3B%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20var%20icon_68d31a893e4a49e3ae49d9ff07a63d17%20%3D%20L.AwesomeMarkers.icon%28%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%22extraClasses%22%3A%20%22fa-rotate-0%22%2C%20%22icon%22%3A%20%22info-sign%22%2C%20%22iconColor%22%3A%20%22white%22%2C%20%22markerColor%22%3A%20%22red%22%2C%20%22prefix%22%3A%20%22glyphicon%22%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20%29%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20marker_ea3e5ee5e7fc480b993375b799c8af19.setIcon%28icon_68d31a893e4a49e3ae49d9ff07a63d17%29%3B%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20var%20popup_0de93e86f2124298a7944d1e15d195db%20%3D%20L.popup%28%7B%22maxWidth%22%3A%20%22100%25%22%7D%29%3B%0A%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20var%20html_a1b5e56ecc3444a9ad676216f8d1da2c%20%3D%20%24%28%60%3Cdiv%20id%3D%22html_a1b5e56ecc3444a9ad676216f8d1da2c%22%20style%3D%22width%3A%20100.0%25%3B%20height%3A%20100.0%25%3B%22%3E%3Cb%3EFim%3C/b%3E%3C/div%3E%60%29%5B0%5D%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20popup_0de93e86f2124298a7944d1e15d195db.setContent%28html_a1b5e56ecc3444a9ad676216f8d1da2c%29%3B%0A%20%20%20%20%20%20%20%20%0A%0A%20%20%20%20%20%20%20%20marker_ea3e5ee5e7fc480b993375b799c8af19.bindPopup%28popup_0de93e86f2124298a7944d1e15d195db%29%0A%20%20%20%20%20%20%20%20%3B%0A%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20marker_ea3e5ee5e7fc480b993375b799c8af19.bindTooltip%28%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%60%3Cdiv%3E%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Click%20me%21%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%3C/div%3E%60%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%22sticky%22%3A%20true%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20%29%3B%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20var%20polygon_66db57d295964afd9bf56dea82797962%20%3D%20L.polygon%28%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%5B%5B%5B-25.4793471%2C%20-54.5830084%5D%2C%20%5B-25.4791589%2C%20-54.5820143%5D%2C%20%5B-25.4790094%2C%20-54.5811864%5D%2C%20%5B-25.4788939%2C%20-54.5804279%5D%2C%20%5B-25.4787659%2C%20-54.5796778%5D%2C%20%5B-25.4786242%2C%20-54.5789065%5D%2C%20%5B-25.4785051%2C%20-54.5781562%5D%2C%20%5B-25.4783906%2C%20-54.5773922%5D%2C%20%5B-25.47825%2C%20-54.576637%5D%5D%2C%20%5B%5B-25.4686043%2C%20-54.5910906%5D%2C%20%5B-25.4700911%2C%20-54.5909344%5D%2C%20%5B-25.4712574%2C%20-54.5907897%5D%5D%2C%20%5B%5B-25.4739268%2C%20-54.5853795%5D%2C%20%5B-25.4739413%2C%20-54.5860836%5D%2C%20%5B-25.4739455%2C%20-54.5864222%5D%2C%20%5B-25.4739486%2C%20-54.5867488%5D%2C%20%5B-25.4739643%2C%20-54.5873107%5D%2C%20%5B-25.4739994%2C%20-54.5885786%5D%5D%2C%20%5B%5B-25.4739994%2C%20-54.5885786%5D%2C%20%5B-25.4739295%2C%20-54.5885911%5D%2C%20%5B-25.4738678%2C%20-54.588597%5D%2C%20%5B-25.4737152%2C%20-54.5886183%5D%2C%20%5B-25.4736634%2C%20-54.5886236%5D%2C%20%5B-25.4731191%2C%20-54.5886912%5D%2C%20%5B-25.4725097%2C%20-54.588769%5D%2C%20%5B-25.4715719%2C%20-54.5888704%5D%5D%2C%20%5B%5B-25.4715719%2C%20-54.5888704%5D%2C%20%5B-25.4709475%2C%20-54.5889328%5D%2C%20%5B-25.4708585%2C%20-54.5889422%5D%2C%20%5B-25.470764%2C%20-54.5889509%5D%2C%20%5B-25.4695844%2C%20-54.5890615%5D%2C%20%5B-25.4691274%2C%20-54.5890796%5D%2C%20%5B-25.4688901%2C%20-54.589093%5D%2C%20%5B-25.467508%2C%20-54.5892084%5D%2C%20%5B-25.4672897%2C%20-54.5892318%5D%2C%20%5B-25.4665726%2C%20-54.589315%5D%2C%20%5B-25.4663165%2C%20-54.5893371%5D%2C%20%5B-25.4660368%2C%20-54.58937%5D%2C%20%5B-25.4658201%2C%20-54.5893928%5D%2C%20%5B-25.4657184%2C%20-54.5893901%5D%2C%20%5B-25.4656718%2C%20-54.5893767%5D%2C%20%5B-25.46563%2C%20-54.5893532%5D%2C%20%5B-25.4655561%2C%20-54.5892875%5D%2C%20%5B-25.4654239%2C%20-54.5892194%5D%2C%20%5B-25.4653185%2C%20-54.5891872%5D%2C%20%5B-25.4624698%2C%20-54.5886485%5D%5D%2C%20%5B%5B-25.4707372%2C%20-54.5936721%5D%2C%20%5B-25.4709253%2C%20-54.5925642%5D%2C%20%5B-25.470968%2C%20-54.5923126%5D%2C%20%5B-25.4710943%2C%20-54.5917457%5D%2C%20%5B-25.4712023%2C%20-54.5911627%5D%2C%20%5B-25.4712187%2C%20-54.5910272%5D%2C%20%5B-25.4712398%2C%20-54.5908985%5D%2C%20%5B-25.4712574%2C%20-54.5907897%5D%5D%2C%20%5B%5B-25.4712574%2C%20-54.5907897%5D%2C%20%5B-25.4712952%2C%20-54.590575%5D%2C%20%5B-25.4713219%2C%20-54.5903496%5D%2C%20%5B-25.4713431%2C%20-54.5902243%5D%2C%20%5B-25.4713682%2C%20-54.5900553%5D%2C%20%5B-25.4713921%2C%20-54.5899517%5D%2C%20%5B-25.4714124%2C%20-54.5898417%5D%2C%20%5B-25.4714448%2C%20-54.5896831%5D%2C%20%5B-25.4714862%2C%20-54.5894692%5D%2C%20%5B-25.4715719%2C%20-54.5888704%5D%5D%2C%20%5B%5B-25.4691683%2C%20-54.5849557%5D%2C%20%5B-25.4694093%2C%20-54.5850307%5D%2C%20%5B-25.4696825%2C%20-54.5851099%5D%2C%20%5B-25.4698708%2C%20-54.5851683%5D%2C%20%5B-25.4699344%2C%20-54.5851844%5D%2C%20%5B-25.4709375%2C%20-54.5853928%5D%2C%20%5B-25.4712754%2C%20-54.5854343%5D%2C%20%5B-25.4716148%2C%20-54.5854759%5D%2C%20%5B-25.4722964%2C%20-54.5854898%5D%2C%20%5B-25.4724876%2C%20-54.5854884%5D%2C%20%5B-25.4731844%2C%20-54.5854576%5D%2C%20%5B-25.4737247%2C%20-54.5854009%5D%2C%20%5B-25.4739268%2C%20-54.5853795%5D%5D%2C%20%5B%5B-25.4739268%2C%20-54.5853795%5D%2C%20%5B-25.474244%2C%20-54.5853265%5D%2C%20%5B-25.4747217%2C%20-54.5852432%5D%2C%20%5B-25.4752271%2C%20-54.5851435%5D%2C%20%5B-25.4753428%2C%20-54.5851169%5D%2C%20%5B-25.4762726%2C%20-54.5847887%5D%2C%20%5B-25.4767817%2C%20-54.5845771%5D%2C%20%5B-25.4771568%2C%20-54.5844213%5D%2C%20%5B-25.4775403%2C%20-54.5842477%5D%2C%20%5B-25.4776939%2C%20-54.5841782%5D%2C%20%5B-25.4781146%2C%20-54.5839646%5D%2C%20%5B-25.4785191%2C%20-54.5837583%5D%2C%20%5B-25.4786446%2C%20-54.5836943%5D%5D%2C%20%5B%5B-25.478772%2C%20-54.5836293%5D%2C%20%5B-25.4789529%2C%20-54.583537%5D%2C%20%5B-25.480124%2C%20-54.5830063%5D%2C%20%5B-25.4807611%2C%20-54.582755%5D%2C%20%5B-25.481025%2C%20-54.5826732%5D%2C%20%5B-25.4812805%2C%20-54.5826222%5D%2C%20%5B-25.481502%2C%20-54.582594%5D%2C%20%5B-25.4817163%2C%20-54.5825779%5D%2C%20%5B-25.4819778%2C%20-54.5825793%5D%2C%20%5B-25.483404%2C%20-54.5826678%5D%2C%20%5B-25.4855476%2C%20-54.5828267%5D%2C%20%5B-25.4864523%2C%20-54.5828938%5D%2C%20%5B-25.4871708%2C%20-54.5829503%5D%2C%20%5B-25.4878317%2C%20-54.5830037%5D%2C%20%5B-25.4888662%2C%20-54.5830809%5D%2C%20%5B-25.4897737%2C%20-54.5831704%5D%2C%20%5B-25.4901374%2C%20-54.583196%5D%5D%2C%20%5B%5B-25.4786446%2C%20-54.5836943%5D%2C%20%5B-25.478772%2C%20-54.5836293%5D%5D%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%7B%22bubblingMouseEvents%22%3A%20true%2C%20%22color%22%3A%20%22%233388ff%22%2C%20%22dashArray%22%3A%20null%2C%20%22dashOffset%22%3A%20null%2C%20%22fill%22%3A%20false%2C%20%22fillColor%22%3A%20%22%233388ff%22%2C%20%22fillOpacity%22%3A%200.2%2C%20%22fillRule%22%3A%20%22evenodd%22%2C%20%22lineCap%22%3A%20%22round%22%2C%20%22lineJoin%22%3A%20%22round%22%2C%20%22noClip%22%3A%20false%2C%20%22opacity%22%3A%201.0%2C%20%22smoothFactor%22%3A%201.0%2C%20%22stroke%22%3A%20true%2C%20%22weight%22%3A%203%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20%29.addTo%28map_92f1cd33c95044be8d74b47a5ba7cf9c%29%3B%0A%20%20%20%20%20%20%20%20%0A%3C/script%3E onload=\"this.contentDocument.open();this.contentDocument.write(    decodeURIComponent(this.getAttribute('data-html')));this.contentDocument.close();\" allowfullscreen webkitallowfullscreen mozallowfullscreen></iframe></div></div>"
      ],
      "text/plain": [
       "<folium.folium.Map at 0x7f8b12411880>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#\n",
    "\n",
    "import folium\n",
    "\n",
    "start_point_arr = [-25.4695946, -54.5909028]\n",
    "end_point_arr = [-25.4786993, -54.57938]\n",
    "tooltip = \"Click me!\"\n",
    "# 3857\n",
    "m = folium.Map(\n",
    "    location=[-25.5172662, -54.6170038],\n",
    "    zoom_start=12,\n",
    "    tiles=\"OpenStreetMap\",\n",
    "    crs=\"EPSG3857\",\n",
    ")\n",
    "folium.Marker(\n",
    "    start_point_arr,\n",
    "    popup=\"<i>Inicio</i>\",\n",
    "    tooltip=tooltip,\n",
    "    icon=folium.Icon(color=\"green\"),\n",
    ").add_to(m)\n",
    "folium.Marker(\n",
    "    end_point_arr, popup=\"<b>Fim</b>\", tooltip=tooltip, icon=folium.Icon(color=\"red\")\n",
    ").add_to(m)\n",
    "\n",
    "# lines = folium.vector_layers.PolyLine(locations=coordinates)\n",
    "# lines.add_to(m)\n",
    "\n",
    "# polygon = folium.vector_layers.Polygon(locations=coordinates_teste)\n",
    "# polygon.add_to(m)\n",
    "\n",
    "polygon_path = folium.vector_layers.Polygon(locations=short_path_coordinates)\n",
    "polygon_path.add_to(m)\n",
    "\n",
    "m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "aff07ad2",
   "metadata": {},
   "outputs": [],
   "source": [
    "spark.stop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9cb5f8a3",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
